﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using NPOI;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;

using System.Web.Services;
using BLL;
using Model;

namespace PropertyAppraisal.ProjectAppraisal
{
    public partial class PingGuPager : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 绑定目的下拉框
        /// </summary>
        /// <returns></returns>
        public static string mudiListBD()
        {
            DictionaryBll Dictionarybll = new DictionaryBll();
            List<SysDictionary> Dictionary = Dictionarybll.sb(39);
            var HtmlStr = "";
            for (var i = 0; i < Dictionary.Count; i++)
            {
                HtmlStr += "<option value=\"" + Dictionary[i].Dictionary_Number + "\">" + Dictionary[i].dictionary_Name + "</option>";
            }
            return HtmlStr;
        }
        /// <summary>
        /// 提交评估
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public static bool submintPing(int mudi, DateTime actDate, DateTime overDate, DateTime priceDate, decimal danjia, decimal mianji, decimal zong, string excel, string fujian, string proId)
        {
            BLL.Basic_InfoBll bll = new BLL.Basic_InfoBll();

            Model.EABasic_Info b = new Model.EABasic_Info();
            b.basic_Evaluation = mudi;
            b.survey_StartTime = actDate;
            b.survey_EndTime = overDate;
            b.basic_ValueTime = priceDate;
            b.basic_UnitPrice = danjia;
            b.basic_Acreage = mianji;
            b.basic_Total = zong;
            b.basic_Excl = excel;
            b.basic_Enclosure = fujian;
            b.projectNumber = proId;


            var result = bll.updPinggu(b);
            return result;
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            //判断上传文件不能为空
            if (FileUpload1.FileName == "")
            {
                this.Label1.Text = "上传文件不能为空";
                return;
            }
            this.FileUpload1.SaveAs(Server.MapPath("/files/") + FileUpload1.FileName);
            string pathurl = ("/files/") + FileUpload1.FileName;
            TextBox6.Text = pathurl;
            DataTable table = this.ImportExcelFile(pathurl);// 根据路径获取要导入的excel文件信息
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DateTime actionDate = Convert.ToDateTime(table.Rows[i][0]);//获取价格
                DateTime overDate = Convert.ToDateTime(table.Rows[i][1]);//获取优惠价格
                DateTime basic_ValueTime = Convert.ToDateTime(table.Rows[i][2]);//获取套餐名称
                decimal basic_UnitPrice = Convert.ToDecimal(table.Rows[i][3]);//获取套餐介绍
                decimal basic_Acreage = Convert.ToDecimal(table.Rows[i][4]);//获取套餐介绍

                TextBox1.Text = actionDate.ToString();
                TextBox2.Text = overDate.ToString();
                TextBox3.Text = basic_ValueTime.ToString();
                TextBox4.Text = basic_UnitPrice.ToString();
                TextBox5.Text = basic_Acreage.ToString();

            }



        }


        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ImportExcelFile(string filePath)
        {
            HSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(Server.MapPath(filePath), FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);

            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(0);//第一行为标题行
            int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
            int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
            //handling header.
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = GetCellValue(row.GetCell(j));
                    }
                }
                table.Rows.Add(dataRow);
            }

            //}
            return table;
        }

        /// <summary>
        /// 根据excel列的类型获取列的值
        /// </summary>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            //判断上传文件不能为空
            if (FileUpload2.FileName == "")
            {
                this.Label2.Text = "上传文件不能为空";
                return;
            }
            this.FileUpload2.SaveAs(Server.MapPath("/files/") + FileUpload2.FileName);
            string pathur2 = ("/files/") + FileUpload2.FileName;
            TextBox7.Text = pathur2;
        }
    }
}